package com.weic.easycache.framework.db;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.CloseableIterator;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.QueryBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import com.weic.easycache.framework.Config;
import com.weic.easycache.framework.KeyUtils;
import com.weic.easycache.framework.web.Api;
import com.weic.easycache.L;

public abstract class AbsOrmLiteHelper extends OrmLiteSqliteOpenHelper {
	public static String TAG = "AbsOrmLiteHelper";

	private boolean mCreatingOrUpgrading = false;
	private boolean mSupportApiCacheTable = false;

	/**如果用OpenHelperManager.getHelper(this, DBHelper.class)，则要用下面的构造方法**/
	public AbsOrmLiteHelper(Context context, String dbName, int dbVersion, boolean supportApiCacheTable) {
		super(context, dbName, null, dbVersion);
		mSupportApiCacheTable = supportApiCacheTable;
	}

	protected abstract IUpgrader getUpgrader();

	@Override
	public synchronized SQLiteDatabase getReadableDatabase() {
		checkIsInitializing();
		return super.getReadableDatabase();
	}

	@Override
	public synchronized SQLiteDatabase getWritableDatabase() {
		checkIsInitializing();
		return super.getWritableDatabase();
	}

	@Override
	public ConnectionSource getConnectionSource() {
		checkIsInitializing();
		return super.getConnectionSource();
	}

	private void checkIsInitializing() {
		//此时外部不可以操作数据库，因为还没有创建或升级完成，创建表的时候更不可以调用get方法获取连接，而应该使用方法携带的参数
		if(mCreatingOrUpgrading) throw new IllegalStateException("不能在onCreate()或onUpgrade()未结束的时候调用本方法");
	}

	@Override
	public void onCreate(SQLiteDatabase database, ConnectionSource connSource) {
		L.d(TAG, "onCreate--------");
		mCreatingOrUpgrading = true;

		if(mSupportApiCacheTable) {
			//创建接口数据缓存表
			try {
				TableUtils.createTableIfNotExists(connSource, Table.ApiCache.clazz);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		Class<?>[] clazs = getTableClasses();
		if(clazs == null || clazs.length == 0) {
			return;
		}
		/* 过滤接口数据缓存表（虽然无法创建与接口数据缓存表相同的类名，但是可以通过注解指定相同的表名称。
		 * 下面的创建语句已经实现了过滤，这里省略）*/
		for(Class<?> clazz : clazs) {
			try {
				TableUtils.createTableIfNotExists(connSource, clazz);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		mCreatingOrUpgrading = false;
		L.d(TAG, "onCreate------END");
	}

	@Override
	public void onUpgrade(SQLiteDatabase database, ConnectionSource connSource, int oldVersion, int newVersion) {
		mCreatingOrUpgrading = true;

		L.i(TAG, "Tables In DB: ");
		//读取旧表信息
		Map<String, String> oldTableSqls = getOldTableSqls(database, false, null);

		//没有旧表，直接创建
		if(oldTableSqls == null || oldTableSqls.size() == 0) {
			onCreate(database, connSource);
			return;	//done
		}
		if(mSupportApiCacheTable) {
		String apiCacheTableSql = getApiCacheTableSql(connSource);
		String apiCacheTableName = parseTableNameInSql(apiCacheTableSql, false, null);

		//接口数据缓存表升级
		String oldApiCacheTableSql = oldTableSqls.remove(apiCacheTableName);
		if(oldApiCacheTableSql != null) {
			if(!apiCacheTableSql.equals(oldApiCacheTableSql)) {
				upgradeTable(database, connSource, getApiCacheTableInner().clazz, apiCacheTableName, oldApiCacheTableSql, apiCacheTableSql);
			}
		}
		//接口数据缓存表导Api数据
		L.d(TAG, "接口数据缓存表导Api数据: ");
		Dao<ApiCacheTable, String> dao = null;
		try {
			dao = getDao(sApiCacheTable.clazz);
			Set<String> oldApiCategorys = getOldApiCategorys(dao);
			if(oldApiCategorys != null && oldApiCategorys.size() > 0) {
				Api<?>[] newApis = sConfig.getApis();
				if(newApis != null && newApis.length > 0) {
					String category;
					for(Api<?> api : newApis) {
						category = KeyUtils.getCategory(api);
						L.d(TAG, "接口数据缓存表导Api数据: "+category);
						if(oldApiCategorys.remove(category)) {
							if(api.cacheTimeMS >= 0) {
								upgradeApiCache(oldVersion, dao, api, category);
							}else {
								L.d(TAG, "cacheTimeMS < 0 删除不用缓存的数据。category: "+category);
								DeleteBuilder<ApiCacheTable, String> deleteBuilder = dao.deleteBuilder();
								deleteBuilder.where().eq(ApiCacheTable.FIELD_CATEGORY, category);
								deleteBuilder.delete();
							}
						}
					}
					L.d(TAG, "接口数据缓存表导Api数据完毕，删除多余数据。names: "+oldApiCategorys);
					DeleteBuilder<ApiCacheTable, String> deleteBuilder = dao.deleteBuilder();
					deleteBuilder.where().in(ApiCacheTable.FIELD_CATEGORY, oldApiCategorys);
					deleteBuilder.delete();
				}else {
					L.w(TAG, "没有接口，清空表------------");
					dao.deleteBuilder().delete();	//清空表
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			L.e(TAG, "接口数据缓存表导Api数据异常：" + e.getLocalizedMessage());
		} finally {
			if(dao != null) {
				try {
					dao.closeLastIterator();
					dao.clearObjectCache();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				dao = null;
			}
		}
	}
		L.i(TAG, "New Tables: ");

		Class<?>[] clazs = getTableClasses();
		if(clazs == null || clazs.length == 0) {
			//删除所有旧表
			deleteTables(database, oldTableSqls.keySet().toArray(new String[oldTableSqls.size()]));

			L.w(TAG, "更新版本：新数据库表列表为空，将清空所有旧表。oldVersion: " + oldVersion + ", newVersion: " + newVersion);
			return;	//done
		}

		Map<String, Class<?>> newTables = new HashMap<String, Class<?>>(clazs.length);
		Map<String, String> newTableSqls = new HashMap<String, String>(clazs.length);
		makeNewTableSqls(connSource, clazs, newTables, newTableSqls, true, apiCacheTableName);

		L.i(TAG, "Upgrade Tables: ");

		Set<String> nameKeys = newTableSqls.keySet();
		String createSql, oldSql;
		for(String name : nameKeys) {
			createSql = newTableSqls.get(name);
			oldSql = oldTableSqls.remove(name);
			if(oldSql == null) {
				//简化处理，只有表名相同的才进行升级，最终没有匹配的旧表将被删除，新表会全部创建
				//旧表不存在。这里先不创建新表，最后一次性创建
				continue;
			}
			if(createSql.equals(oldSql)) {	//表结构没有变更，不用导数据
				L.i(TAG, "Upgrade Tables。表结构没有变更： " + name);
				//newTableSqls不删除，需要在最后再执行一遍创建，因为TableUtils.getCreateTableStatements()返回是是一个list。
				continue;	//表结构相同，不用执行升级操作
			}else {
				if(getUpgrader().needSaveOldTableData(oldVersion, name, newTables.get(name))) {
					//导数据
					upgradeTable(database, connSource, newTables.get(name), name, oldSql, createSql);
				}else{
					//删除旧表
					deleteTable(database, name);
				}
			}
		}
		//删除多余的旧表
		deleteTables(database, oldTableSqls.keySet().toArray(new String[oldTableSqls.size()]));

		//创建所有没有创建的表，避免遗漏
		onCreate(database, connSource);

		mCreatingOrUpgrading = false;
	}

	private static Class<?>[] tableClazs;
	private static Class<?>[] getTableClasses() {
		if(tableClazs == null) {
			Table<?, ?>[] tables = sConfig.getTables();
			if(tables == null || tables.length == 0) {
				tableClazs = null;
			}else {
				tableClazs = new Class<?>[tables.length];
				for(int i=0; i<tables.length; i++) {
					tableClazs[i] = tables[i].clazz;
				}
			}
		}
		return tableClazs;
	}

	private Set<String> getOldApiCategorys(Dao<ApiCacheTable, String> dao) {
		Set<String> categorys = null;
		try {
			List<ApiCacheTable> list = dao.queryBuilder().distinct().selectColumns(ApiCacheTable.FIELD_CATEGORY).query();
			if(list != null) {
				categorys = new HashSet<String>(list.size());
				for(ApiCacheTable table : list) {
					categorys.add(table.category);
					L.i(TAG, "OldApiCategory: "+table.category);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return categorys;
	}

	private CloseableIterator<ApiCacheTable> getApiCacheList(Dao<ApiCacheTable, String> dao, String categoryValue) {
		CloseableIterator<ApiCacheTable> iterator = null;
		try {
			QueryBuilder<ApiCacheTable, String> queryBuilder = dao.queryBuilder();
			queryBuilder.selectColumns(ApiCacheTable.FIELD_KEY);
			iterator = queryBuilder.where().eq(ApiCacheTable.FIELD_CATEGORY, categoryValue).iterator();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return iterator;
	}

	private void upgradeApiCache(int oldVersion, Dao<ApiCacheTable, String> dao, Api<?> newApi, String category) {
		CloseableIterator<ApiCacheTable> iterator = getApiCacheList(dao, category);
		if(iterator != null) {
			String newKey;
			boolean test = true, saveOldData = true;
			ApiCacheTable table;
			while(iterator.hasNext()) {
				table = iterator.next();
				try {
					if(test) {
						if(!KeyUtils.isDBCacheKeyChanged(table.key, newApi)) {
							L.d(TAG, "[upgradeApiCache] 没有改变: "+category);
							break;
						}
						saveOldData = sConfig.getUpgrader().needSaveOldApiData(oldVersion, newApi.baseUrl, newApi.name, newApi);
						L.w(TAG, "[upgradeApiCache] 是否保存旧数据: "+saveOldData+", "+category);
						if(!saveOldData) {
							break;
						}
						test = false;
					}
					newKey = KeyUtils.updateDBCacheKey(table.key, newApi);
					if(newKey == null || newKey.length() == 0) {
						//delete
						dao.deleteById(table.key);
						L.d(TAG, "[upgradeApiCache] 删除: "+table.key);
					}else if(newKey.equals(table.key)) {
						//保持不变
						L.d(TAG, "[upgradeApiCache] 保持不变: "+table.key);
					}else {
						//注意每次调用dao.updateBuilder()都会new一个新的
						UpdateBuilder<ApiCacheTable, String> updateBuilder = dao.updateBuilder();
						updateBuilder.where().eq(ApiCacheTable.FIELD_KEY, table.key);
						updateBuilder.updateColumnValue(ApiCacheTable.FIELD_KEY, newKey);
						L.d(TAG, "[upgradeApiCache] 更新语句: "+updateBuilder.prepareStatementString());
						updateBuilder.update();
						L.d(TAG, "[upgradeApiCache] 更新 oldkey: "+table.key+", newkey: "+newKey);
					}
				}catch(SQLException e) {
					e.printStackTrace();
					L.e(TAG, "[upgradeApiCache] category: "+category+", SQLException: "+e.getLocalizedMessage());
					L.e(TAG, "发生异常，则清空数据。category: "+category);
					saveOldData = false;
				}
			}
			try {
				iterator.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			if(!saveOldData) {
				try {
					DeleteBuilder<ApiCacheTable, String> deleteBuilder = dao.deleteBuilder();
					deleteBuilder.where().eq(ApiCacheTable.FIELD_CATEGORY, category);
					deleteBuilder.delete();
					L.w(TAG, "[upgradeApiCache] 删除旧数据。"+category);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private Map<String, String> getOldTableSqls(SQLiteDatabase database, boolean except, String exceptName) {
		Map<String, String> sqls = null;
		String createSql;
		String tableName;
		Cursor cursor = database.query("sqlite_master", new String[]{"sql"}, "type='table'", null, null, null, "tbl_name");
		if(cursor != null) {
			sqls = new HashMap<String, String>(cursor.getCount());
			while(cursor.moveToNext()) {
				createSql = cursor.getString(0).trim();
				tableName = parseTableNameInSql(createSql, except, exceptName);
				if(tableName == null) continue;	//注意有些是系统表
				sqls.put(tableName, createSql);

				L.i(TAG, "TableName: "+tableName+", SQL: "+createSql);
			}
			cursor.close();
		}
		return sqls;
	}

	private void makeNewTableSqls(ConnectionSource connSource, Class<?>[] clazs, Map<String, Class<?>> newTables, Map<String, String> newTableSqls, boolean except, String exceptName) {
		List<String> statements;
		String tableName;
		for(Class<?> clazz : clazs) {
			try {
				//为什么是个List，暂时忽略，测试只看到一个元素，即创建表的语句。
				statements = TableUtils.getCreateTableStatements(connSource, clazz);
				if(statements != null) {
					for(String sql : statements) {
						sql = sql.trim();	//trim()很重要
						tableName = parseTableNameInSql(sql, except, exceptName);
						if(tableName == null) continue;
						newTableSqls.put(tableName, sql);
						newTables.put(tableName, clazz);

						L.i(TAG, "Table Class: "+clazz.getSimpleName()+", TableName: "+tableName+", SQL: "+sql);
					}
				}
			} catch (SQLException e) {
				L.e(TAG, "Table Class: "+clazz.getSimpleName()+", SQLException: "+e.getLocalizedMessage());
			}
		}
	}

	private static String getApiCacheTableSql(ConnectionSource connSource) {
		String tableSql = null;
		try {
			List<String> statements = TableUtils.getCreateTableStatements(connSource, sApiCacheTable.clazz);
			if(statements != null) {
				for(String sql : statements) {
					tableSql = sql.trim();	//trim()很重要
					if(tableSql == null) continue;
					L.i(TAG, "ApiCacheTableName: "+sApiCacheTable.clazz.getSimpleName()+", ApiCacheTableSql: "+tableSql);
					break;
				}
			}
		} catch (SQLException e) {
			L.e(TAG, "ApiCacheTableName: "+sApiCacheTable.clazz.getSimpleName()+", SQLException: "+e.getLocalizedMessage());
		}
		return tableSql;
	}

	/**导数据。查询数据到内存，删除旧表，创建新表，写入数据**/
	private static void upgradeTable(SQLiteDatabase database, ConnectionSource connSource, Class<?> tableClazz, String name, String oldSql, String createSql) {
		L.i(TAG, "[upgradeTable start]Table: " + name);

		String[] fields  = getSameFields(oldSql, createSql);
		if(fields == null || fields.length == 0) return;

		String fstr = "";
		boolean b = false;
		for(String s : fields) {
			if(b) {
				fstr += ", ";
			}else {
				b = true;
			}
			fstr += DOT + s + DOT;
		}

		String name_backup = DOT + name + "_backup" + DOT;
		name = DOT + name + DOT;
		//以下事务是官方写法
		database.beginTransaction();
		try {
			//导入有效数据
			database.execSQL("CREATE TEMPORARY TABLE " + name_backup + "(" + fstr + ")");
			database.execSQL("INSERT INTO " + name_backup + " SELECT " + fstr + " FROM " + name);
			database.execSQL("DROP TABLE " + name);
			TableUtils.createTable(connSource, tableClazz);
			database.execSQL("INSERT INTO " + name + "(" + fstr + ")" + " SELECT " + fstr + " FROM " + name_backup);
			database.execSQL("DROP TABLE " + name_backup);

			database.setTransactionSuccessful();
			L.i(TAG, "[upgradeTable Successful]Table: " + name);
		} catch(Exception e) {
			database.execSQL("DROP TABLE " + name);
			L.i(TAG, "[upgradeTable Exception][SQL]" + "DROP TABLE " + name);
		} finally {
			database.endTransaction();
		}
		sConfig.getUpgrader().
	}

	private static void deleteTables(SQLiteDatabase database, String[] tableNames) {
		for(String table : tableNames) {
			deleteTable(database, table);
		}
	}

	private static void deleteTable(SQLiteDatabase database, String tableName) {
		tableName = DOT + tableName + DOT;
		String sql = "DROP TABLE IF EXISTS " + tableName;
		L.w(TAG, "[deleteTable][SQL]" + sql);
		database.execSQL(sql);
	}

	private static String parseTableNameInSql(String createSql, boolean except, String exceptName) {
		if(!createSql.substring(0, 12).equalsIgnoreCase("CREATE TABLE")) {
			L.i(TAG, "[getTableName] not create SQL: "+createSql);
			return null;
		}
		int $ = createSql.indexOf('(');	//注意有可能是CREATE TABLE IF NOT EXISTS
		String sys = createSql.substring(0, $);
		if(sys.contains("android_") || sys.contains("sqlite_") || sys.contains("metadata")) {
			L.i(TAG, "[getTableName] systable SQL: "+createSql);
			return null;
		}

		int s = createSql.indexOf(DOT) + 1;
		int e = createSql.indexOf(DOT, s);
		if(e <= 0 || e > $) {	//系统表中没有这些字符
			L.i(TAG, "[getTableName]Index s: "+s+", e: "+e+", SQL: "+createSql);
			return null;
		}
		String name = createSql.substring(s, e);
		if(except && name.equals(exceptName)) {
			L.i(TAG, "[getTableName] except exceptName: "+exceptName);
			return null;
		}
		return name;
	}

	private static String[] getSameFields(String oldSql, String createSql) {
		List<String> list = new LinkedList<String>();
		oldSql = oldSql.substring(oldSql.indexOf('(') + 1, oldSql.indexOf(')'));
		createSql = createSql.substring(createSql.indexOf('(') + 1, createSql.indexOf(')'));

		String[] oldFields = oldSql.split(",");
		String[] newFields = createSql.split(",");

		for(int i=0; i<2; i++) {
			String[] arr = i==0 ? oldFields : newFields;
			for(int j=0; j<arr.length; j++) {
				arr[j] = arr[j].trim();
			}
		}
		if(oldFields.length > newFields.length) {
			String[] arr = newFields;
			newFields = oldFields;
			oldFields = arr;
		}
		List<String> list2 = new LinkedList<String>();
		for(String s : oldFields) {
			list2.add(s);
		}
		String fieldName;
		for(String s : newFields) {
			for(int i=0, len=list2.size(); i<len; i++) {
				if(s.equalsIgnoreCase(list2.get(i))) {
					fieldName = parseFieldName(s);
					if(fieldName != null && fieldName.length() > 0) list.add(fieldName);
					list2.remove(i);
					break;
				}
			}
		}
		return list.toArray(new String[list.size()]);
	}

	private static String parseFieldName(String field) {
		String result;
		int index = field.indexOf(DOT);
		if(index >= 0) {
			int $index = field.indexOf('(');
			if($index >= 0 && $index < index) {	//PRIMARY KEY (`key_param`)
				result = null;
				L.i(TAG, "[parseFieldName] 带有括号(``): "+field);
			}else {
				index++;
				result = field.substring(index, field.indexOf(DOT, index));
			}
		}else {
			char firstChar = field.charAt(0);
			if(firstChar >= 'A' && firstChar <= 'Z') {	//数据库关键字
				result = null;
				L.i(TAG, "[parseFieldName] 数据库关键字开头: "+firstChar);
			}else {
				result = field.split(" ")[0].trim();
			}
		}
		L.i(TAG, "[parseFieldName] result: "+result);
		return result;
	}

	public static char DOT = '`';
}
